{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "11470\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "market = \"hair_dryer\"\n",
    "# market = \"microwave\"\n",
    "# market = \"pacifier\"\n",
    "\n",
    "inputexcel = pd.read_excel(\"../Problem_C_Data/\" + market + '.xlsx', market)\n",
    "# 评论的个数\n",
    "num_review = len(list(inputexcel['star_rating']))\n",
    "print(num_review)\n",
    "\n",
    "xlsxxlsx = '../Problem_C_Data/good_contain.xls'\n",
    "good_words = pd.read_excel(xlsxxlsx, 'Sheet1')\n",
    "good_words = good_words.key.values.tolist()\n",
    "xlsxxlsx = '../Problem_C_Data/bad_contain.xls'\n",
    "bad_words = pd.read_excel(xlsxxlsx, 'Sheet1')\n",
    "bad_words = bad_words.key.values.tolist()\n",
    "# print(bad_words[1:5])\n",
    "# good_words[3]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "11470\n",
      "['4', 'good', 'quiet', 'small', 'works', 'well']\n",
      "['1', 'am', 'loud', 'heavy', 'hot', 'like', 'hot', 'am', 'happy']\n",
      "['5', 'excellent', 'one', 'one', 'worked', 'worked', 'new', 'one', 'one', 'works', 'very', 'convenient', 'very', 'well', 'light', 'much', 'really', 'need', 'great']\n",
      "['4', 'great', 'cord', 'perfect', 'last', 'great', 'cord', 'perfect']\n",
      "['5', 'great', 'soft', 'absolutely', 'very', 'cord', 'very', 'convenient', 'well', 'great', 'job', 'fast', 'loved', 'much', 'one', 'one', 'loves']\n",
      "\n"
     ]
    }
   ],
   "source": [
    "import re\n",
    "\n",
    "# 一行代表一个评论，一行第一个元素是star_rating\n",
    "output = []\n",
    "star_rating_list = list(inputexcel['star_rating'])\n",
    "review_headline_list = list(inputexcel['review_headline'])\n",
    "review_body_list = list(inputexcel['review_body'])\n",
    "\n",
    "for ii in range(num_review):\n",
    "#     用正则表达式替换掉所有不是英文字母和空格的字符变成空格\n",
    "    review_headline = re.sub('[^a-zA-Z\\s]', ' ', str(review_headline_list[ii]))\n",
    "    review_body = re.sub('[^a-zA-Z\\s]', ' ', str(review_body_list[ii]))\n",
    "#     避免review_headline最后一个单词和review_body第一个单词挨在一起，中间加个空格\n",
    "    review = review_headline + ' ' + review_body\n",
    "#     小写 以空格分词\n",
    "    reviews = review.lower().split(' ')\n",
    "    onereview = []\n",
    "#     提取出评论文本中的每个单词\n",
    "    for review_word in reviews:\n",
    "#         如果在好词列表中，那么就加进该评论对应的的词表\n",
    "        if review_word in good_words:\n",
    "            onereview.append(review_word)\n",
    "#         如果在坏词列表中，那么就加进该评论对应的的词表\n",
    "        if review_word in bad_words:\n",
    "            onereview.append(review_word)\n",
    "#     将该评论的词表添加到所有评论词表末尾\n",
    "    output.append(list(str(star_rating_list[ii]))+onereview)\n",
    "\n",
    "# print(star_rating)\n",
    "\n",
    "print(len(output))\n",
    "for r in output[1:6]:\n",
    "        print(r)\n",
    "print()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(frozenset({'stopped'}), frozenset({'working'}), 0.016739319965126415, 0.735632183908046, 17.361524998817462)\n",
      "(frozenset({'working'}), frozenset({'stopped'}), 0.016739319965126415, 0.3950617283950617, 17.361524998817462)\n",
      "(frozenset({'waste'}), frozenset({'money'}), 0.010462074978204011, 0.8, 17.24812030075188)\n",
      "(frozenset({'money'}), frozenset({'waste'}), 0.010462074978204011, 0.22556390977443608, 17.24812030075188)\n",
      "(frozenset({'recommend', '5'}), frozenset({'highly'}), 0.023626852659110725, 0.40088757396449703, 12.03712165804393)\n",
      "(frozenset({'recommend', 'great', '5'}), frozenset({'highly'}), 0.01054925893635571, 0.38412698412698415, 11.533865204022272)\n",
      "(frozenset({'recommend', 'one'}), frozenset({'highly'}), 0.01002615518744551, 0.3463855421686747, 10.400633949410205)\n",
      "(frozenset({'recommend', 'great'}), frozenset({'highly'}), 0.01107236268526591, 0.33159268929503916, 9.956461115743714)\n",
      "(frozenset({'great', 'highly', '5'}), frozenset({'recommend'}), 0.01054925893635571, 0.8175675675675675, 9.52030456852792)\n",
      "(frozenset({'ever', '5'}), frozenset({'best'}), 0.03443766346992153, 0.7655038759689923, 9.380693864705494)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "from akapriori import apriori\n",
    "\n",
    "'''\n",
    "https://github.com/aknd/akapriori\n",
    "https://blog.csdn.net/qq_35515661/article/details/87391328\n",
    "\n",
    "transactions 待处理数据 列表套多元组的格式 [(),()...]\n",
    "\n",
    "support 最小支持度 P(AB) \n",
    "Support(A→B)= P(A∩B) \n",
    "\n",
    "confidence 最小置信度  P(B/A)  条件概率  \n",
    "P(AB)/P(A)\n",
    "Confidence(A→B)=P(B|A)=P(A∩B)/P(A)\n",
    "\n",
    "lift 判断的阈值 1/P(A)  先验概率的倒数  \n",
    "P(AB)/(P(A)P(B)) Lift=1时表示A和B独立。\n",
    "Lift(A→B)=Confidence(A→B)/Support(B)=P(B|A)/P(B)\n",
    "\n",
    "minlen maxlen  候选集最小长度 最大长度\n",
    "\n",
    "'''\n",
    "\n",
    "rules = apriori(output, support=0.01, confidence=0.2, lift=0, minlen=0, maxlen=5)\n",
    "# 根据参数进行排序输出 排序优先级： lift 降序, confidence 降序, support 降序\n",
    "rules_sorted = sorted(rules, key=lambda x: (x[4], x[3], x[2]), reverse=True)\n",
    "# rules_sorted[0]  frozenset\n",
    "# rules_sorted[1]  frozenset\n",
    "# rules_sorted[2]  support 最小支持度 P(AB) \n",
    "# rules_sorted[3]  confidence 最小置信度  P(B/A)  条件概率\n",
    "# rules_sorted[4]  lift 判断的阈值 1/P(A)  先验概率的倒数\n",
    "\n",
    "# 最多打印这么多\n",
    "printnummax  = 10\n",
    "# 输出 rules_sorted\n",
    "if len(rules_sorted) > printnummax:\n",
    "    for r in rules_sorted[0:printnummax]:\n",
    "        print(r)\n",
    "else:\n",
    "    for r in rules_sorted:\n",
    "        print(r)\n",
    "\n",
    "print()\n",
    "\n",
    "\n",
    "# list转dataframe\n",
    "df = pd.DataFrame(rules_sorted, columns=['frozensetA', 'frozensetB', 'support', 'confidence', 'lift'])\n",
    "\n",
    "# 保存到本地excel\n",
    "df.to_excel(\"apriori_star_rating_descriptors_2e_akapriori.xlsx\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(frozenset({'1'}), frozenset({'months'}), 0.01970357454228422, 0.2189922480620155, 3.7212460522538042)\n",
      "(frozenset({'months'}), frozenset({'1'}), 0.01970357454228422, 0.3348148148148148, 3.721246052253804)\n",
      "(frozenset({'working'}), frozenset({'1'}), 0.013251961639058413, 0.31275720164609055, 3.4760902159696303)\n",
      "(frozenset({'money'}), frozenset({'1'}), 0.014472537053182214, 0.31203007518796994, 3.4680086845019527)\n",
      "(frozenset({'back'}), frozenset({'1'}), 0.014734088927637315, 0.24889543446244478, 2.7663087531824044)\n",
      "(frozenset({'never'}), frozenset({'1'}), 0.01002615518744551, 0.222007722007722, 2.467469545957918)\n",
      "(frozenset({'1'}), frozenset({'one'}), 0.03435047951176983, 0.3817829457364341, 1.4461857290610634)\n",
      "(frozenset({'1'}), frozenset({'use'}), 0.021360069747166522, 0.2374031007751938, 1.2194418118636243)\n",
      "(frozenset({'1'}), frozenset({'very'}), 0.020749782040104622, 0.23062015503875968, 0.9304302421015033)\n"
     ]
    }
   ],
   "source": [
    "# 查找所有 star_rating = 1~5 的 rules_sorted\n",
    "rules_sorted_5 = []\n",
    "rules_sorted_4 = []\n",
    "rules_sorted_3 = []\n",
    "rules_sorted_2 = []\n",
    "rules_sorted_1 = []\n",
    "for rules_sort in rules_sorted:\n",
    "    if '5' in rules_sort[0] or '5' in rules_sort[1]:\n",
    "        rules_sorted_5.append(rules_sort)\n",
    "    elif '4' in rules_sort[0] or '4' in rules_sort[1]:\n",
    "        rules_sorted_4.append(rules_sort)\n",
    "    elif '3' in rules_sort[0] or '3' in rules_sort[1]:\n",
    "        rules_sorted_3.append(rules_sort)\n",
    "    elif '2' in rules_sort[0] or '2' in rules_sort[1]:\n",
    "        rules_sorted_2.append(rules_sort)\n",
    "    elif '1' in rules_sort[0] or '1' in rules_sort[1]:\n",
    "        rules_sorted_1.append(rules_sort)\n",
    "\n",
    "# 最多打印这么多\n",
    "printnummax = 10\n",
    "# 要打印哪个 star_rating 的 rules_sorted\n",
    "rules_sorted_print = rules_sorted_1\n",
    "# 输出 rules_sorted_？\n",
    "if len(rules_sorted_print) > printnummax:\n",
    "    for r in rules_sorted_print[0:printnummax]:\n",
    "        print(r)\n",
    "else:\n",
    "    for r in rules_sorted_print:\n",
    "        print(r)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# print(inputexcel['review_headline'][1])\n",
    "# print('good' in good_words)\n",
    "# print(good_words.head())\n",
    "# review_headlines = inputexcel['review_headline'][1].split(' ')\n",
    "# print(review_headlines)\n",
    "# for word in good_words[1:5]:\n",
    "#     print(word)\n",
    "\n",
    "\n",
    "#     print(review_headline)\n",
    "    # review_headline = \"All the First Would Use; *         GOOD Second Wouldn't Touch Them\"\n",
    "    #     print(review_headlines)\n",
    "    # review_headlines = review_headlines.strip()\n",
    "    # review_headlines = [x.strip() for x in review_headline.split(' ')]\n",
    "    #     review_headlines = list(filter(str.isalpha, review_headlines))\n",
    "\n",
    "    # output.append('best')\n",
    "    # output.append('best')\n",
    "    #     print(review_headline)\n",
    "    # review_headline = review_headline.remove('')\n",
    "        #         print(review_word)\n",
    "#             output[ii].append(review_word)\n",
    "#     print(output)\n",
    "#     print()\n",
    "# re.sub('[^a-zA-Z\\s]', ' ', 'N/A')\n",
    "# for i in range(4):\n",
    "#      for j in range(4):\n",
    "#          print((i, j))\n",
    "# list(inputexcel['star_rating'])[1]\n",
    "# str(list(inputexcel['star_rating'])[1])\n",
    "\n",
    "\n",
    "\n",
    "# ii = 2\n",
    "# print(rules_sorted[2:5][0:2])\n",
    "# print(rules_sorted[2:5])\n",
    "\n",
    "\n",
    "    \n",
    "# print('5' in rules_sorted[2:5][0] or '5' in rules_sorted[2:5][1])\n",
    "\n",
    "# print(review_headline)\n",
    "# print(review_body)\n",
    "\n",
    "# print(review)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "# print(rules_sorted[:][1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
